package com.example.demo01.Impl.webImpl;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.example.demo01.Dao.webDao.WarnRecordEntityDao;
import com.example.demo01.Entity.webEntity.NameValue;
import com.example.demo01.Entity.webEntity.WarnDetail;
import com.example.demo01.Entity.webEntity.WarnRecordEntity;
import com.example.demo01.utils.ArrayUtils;
import com.example.demo01.utils.JDBCPGUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class WarnRecordEntityDaoImpl implements WarnRecordEntityDao {
    @Autowired
    JdbcTemplate jdbcTemplate;

    /**
     * 获取报警省份信息
     * */
    @Override
    public List<NameValue> getProvinceOfWarn() {
        String sql="SELECT DISTINCT chinaencode.domain as name,chinaencode.cvalue as value  FROM warnrecord,chinaencode WHERE LEFT(warnrecord.idnumber,2)=LEFT(chinaencode.cvalue,2) AND RIGHT(chinaencode.cvalue,4)='0000'";
        return jdbcTemplate.query(sql, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1).replace("\u00A0", "").trim(),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 根据省份编码获取报警市级信息
     * @param provinceCode 省份编码
     * */
    @Override
    public List<NameValue> getCityOfWarn(String provinceCode) {
        String sql="SELECT DISTINCT chinaencode.domain as name,chinaencode.cvalue as value  FROM warnrecord,chinaencode WHERE LEFT(chinaencode.cvalue,2)=? AND LEFT(warnrecord.idnumber,4)=LEFT(chinaencode.cvalue,4) AND RIGHT(chinaencode.cvalue,2)='00'";
        return jdbcTemplate.query(sql, new Object[]{provinceCode.substring(0,2)}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1).replace("\u00A0", "").trim(),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 根据市级编码获取报警县区信息
     * */
    @Override
    public List<NameValue> getCountryOfWarn(String cityCode) {
        String sql="SELECT DISTINCT chinaencode.domain as name,chinaencode.cvalue as value  FROM warnrecord,chinaencode WHERE LEFT(chinaencode.cvalue,4)=? AND LEFT(warnrecord.idnumber,6)=LEFT(chinaencode.cvalue,6)";
        return jdbcTemplate.query(sql, new Object[]{cityCode.substring(0,4)}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1).replace("\u00A0", "").trim(),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 获取所有报警信息
     * */
    @Override
    public List<WarnRecordEntity> getAllWarnRecords() {
        String sql="SELECT\n" +
                "\t(@i:=@i+1) id,\n" +
                "\twarnrecord.idnumber,\n" +
                "\tuserinfo1.name,\n" +
                "\twarnrecord.wlon,\n" +
                "\twarnrecord.wlat,\n" +
                "\twarnrecord.warntime,\n" +
                "\twarnrecord.status,\n" +
                "\twarnrecord.endtime,\n" +
                "\twarnrecord.warnaddress\n" +
                "FROM warnrecord,userinfo1,(SELECT @i:=0) as i\n" +
                "WHERE warnrecord.idnumber=userinfo1.idnum " +
                "ORDER BY warnrecord.warntime DESC";
        return jdbcTemplate.query(sql, new RowMapper<WarnRecordEntity>() {
            @Nullable
            @Override
            public WarnRecordEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                return new WarnRecordEntity(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getFloat(4),
                        resultSet.getFloat(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getString(9)
                );
            }
        });
    }

    /**
     * 获取待处理的报警信息
     * */
    @Override
    public List<WarnRecordEntity> getUnResolvedWarnRecords() {
        String sql="SELECT\n" +
                "\t(@i:=@i+1) id,\n" +
                "\twarnrecord.idnumber,\n" +
                "\tuserinfo1.name,\n" +
                "\twarnrecord.wlon,\n" +
                "\twarnrecord.wlat,\n" +
                "\twarnrecord.warntime,\n" +
                "\twarnrecord.status,\n" +
                "\twarnrecord.endtime,\n" +
                "\twarnrecord.warnaddress\n" +
                "FROM warnrecord,userinfo1,(SELECT @i:=0) as i\n" +
                "WHERE warnrecord.status=0 AND warnrecord.idnumber=userinfo1.idnum\n" +
                "ORDER BY warnrecord.warntime DESC";
        return jdbcTemplate.query(sql, new RowMapper<WarnRecordEntity>() {
            @Nullable
            @Override
            public WarnRecordEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                return new WarnRecordEntity(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getFloat(4),
                        resultSet.getFloat(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getString(9)
                );
            }
        });
    }

    /**
     * 根据省级编码获取报警信息
     * */
    @Override
    public List<WarnRecordEntity> getWarnRecordByProvinceCode(String provinceCode) {
        String sql="SELECT \n" +
                "\t(@i:=@i+1) id,\n" +
                "\twarnrecord.idnumber,\n" +
                "\tuserinfo1.name,\n" +
                "\twarnrecord.wlon,\n" +
                "\twarnrecord.wlat,\n" +
                "\twarnrecord.warntime,\n" +
                "\twarnrecord.status,\n" +
                "\twarnrecord.endtime,\n" +
                "\twarnrecord.warnaddress\n" +
                "FROM warnrecord,userinfo1,(SELECT @i:=0) as i\n" +
                "WHERE warnrecord.status=1  AND LEFT(userinfo1.idnum,2)=? AND warnrecord.idnumber=userinfo1.idnum\n" +
                "ORDER BY warnrecord.status ASC";
        return jdbcTemplate.query(sql, new Object[]{provinceCode}, new RowMapper<WarnRecordEntity>() {
            @Nullable
            @Override
            public WarnRecordEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                return new WarnRecordEntity(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getFloat(4),
                        resultSet.getFloat(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getString(9)
                );
            }
        });
    }

    /**
     * 根据市级编码获取报警信息
     * */
    @Override
    public List<WarnRecordEntity> getWarnRecordByCityCode(String cityCode) {
        String sql="SELECT \n" +
                "\t(@i:=@i+1) id,\n" +
                "\twarnrecord.idnumber,\n" +
                "\tuserinfo1.name,\n" +
                "\twarnrecord.wlon,\n" +
                "\twarnrecord.wlat,\n" +
                "\twarnrecord.warntime,\n" +
                "\twarnrecord.status,\n" +
                "\twarnrecord.endtime,\n" +
                "\twarnrecord.warnaddress\n" +
                "FROM warnrecord,userinfo1,(SELECT @i:=0) as i\n" +
                "WHERE warnrecord.status=1  AND LEFT(userinfo1.idnum,4)=? AND warnrecord.idnumber=userinfo1.idnum\n" +
                "ORDER BY warnrecord.status ASC";
        return jdbcTemplate.query(sql, new Object[]{cityCode}, new RowMapper<WarnRecordEntity>() {
            @Nullable
            @Override
            public WarnRecordEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                return new WarnRecordEntity(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getFloat(4),
                        resultSet.getFloat(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getString(9)
                );
            }
        });
    }

    /**
     * 根据县区级编码获取报警信息
     * */
    @Override
    public List<WarnRecordEntity> getWarnRecordByCountryCode(String countryCode) {
        String sql="SELECT \n" +
                "\t(@i:=@i+1) id,\n" +
                "\twarnrecord.idnumber,\n" +
                "\tuserinfo1.name,\n" +
                "\twarnrecord.wlon,\n" +
                "\twarnrecord.wlat,\n" +
                "\twarnrecord.warntime,\n" +
                "\twarnrecord.status,\n" +
                "\twarnrecord.endtime,\n" +
                "\twarnrecord.warnaddress\n" +
                "FROM warnrecord,userinfo1,(SELECT @i:=0) as i\n" +
                "WHERE warnrecord.status=1  AND LEFT(userinfo1.idnum,6)=? AND warnrecord.idnumber=userinfo1.idnum\n" +
                "ORDER BY warnrecord.status ASC";
        return jdbcTemplate.query(sql, new Object[]{countryCode}, new RowMapper<WarnRecordEntity>() {
            int countNum=0;
            @Nullable
            @Override
            public WarnRecordEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                return new WarnRecordEntity(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getFloat(4),
                        resultSet.getFloat(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getString(9)
                );
            }
        });
    }

    /**
     * 根据时间限定获取报警信息
     * */
    @Override
    public List<WarnRecordEntity> getWarnRecordByTimeLimted(String startTime, String endTime) {
        String sql="SELECT \n" +
                "\t(@i:=@i+1) id,\n" +
                "\twarnrecord.idnumber,\n" +
                "\tuserinfo1.name,\n" +
                "\twarnrecord.wlon,\n" +
                "\twarnrecord.wlat,\n" +
                "\twarnrecord.warntime,\n" +
                "\twarnrecord.status,\n" +
                "\twarnrecord.endtime,\n" +
                "\twarnrecord.warnaddress\n" +
                "FROM warnrecord,userinfo1,(SELECT @i:=0) as i\n" +
                "WHERE warnrecord.status=1  AND (warnrecord.warntime BETWEEN ? AND ?) AND warnrecord.idnumber=userinfo1.idnum\n" +
                "ORDER BY warnrecord.status ASC";
        return jdbcTemplate.query(sql, new Object[]{startTime, endTime}, new RowMapper<WarnRecordEntity>() {
            @Nullable
            @Override
            public WarnRecordEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                return new WarnRecordEntity(
                       resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getFloat(4),
                        resultSet.getFloat(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getString(9)
                );
            }
        });
    }

    /**
     * 根据行政区划范围获取报警信息点集合
     * @param bounds 行政区划范围顶点数组
     */
    @Override
    public List<WarnRecordEntity> getWarnRecordByBounds(String bounds) {
        //（1）-字符串解析与SQL语句拼接
        String sql = getQuerySqlByRequestSrting(bounds);
        //（2）-数据库操作
        return solveResulotionBySql(sql);
    }

    /**
     * 根据矩形范围获取报警信息点的集合
     * */
    @Override
    public List<WarnRecordEntity> getWarnRecordByRectangle(String vertexColls) {
        //（1）-字符串解析与SQL语句拼接
        String sql = getQuerySqlByRectangleVecs(vertexColls);
        //（2）-数据库操作
        return solveResulotionBySql(sql);
    }

    /**
     * 根据指定的四个边界范围获取报警信息点的集合
     * @param north 北界
     * @param east 东界
     * @param south 南界
     * @param west 西界
     * */
    @Override
    public List<WarnRecordEntity> getWarnRecordByBoundPts(String north, String east, String south, String west) {
        //(1)拼接SQL语句
        String sql="select \n" +
                "\tROW_NUMBER() over(ORDER bY warnrecord.idnumber DESC)  as id,\n" +
                "\twarnrecord.idnumber,\n" +
                "\tuserinfo1.name,\n" +
                "\twarnrecord.wlon,\n" +
                "\twarnrecord.wlat,\n" +
                "\twarnrecord.warntime,\n" +
                "\twarnrecord.status,\n" +
                "\twarnrecord.endtime,\n" +
                "\twarnrecord.warnaddress\n" +
                " from warnrecord,userinfo1,\n" +
                " \t\t(select ST_GeomFromText('POLYGON((";
        sql+=(west+" "+south+","+west+" "+north+","+east+" "+north+","+east+" "+south+","+west+" "+south);
        sql+="))', 4326) as geom) A\n" +
                " where st_within(st_lonlatToGeomPoint(warnrecord.wlon,warnrecord.wlat),A.geom)='t' and userinfo1.idnum=warnrecord.idnumber";
        //（2）执行数据查询
        return solveResulotionBySql(sql);
    }

    /**
     * 根据用户身份证号获取用户详细的报警信息
     * @param idnum
     * @return WarnDetail实例
     */
    @Override
    public WarnDetail getWarnDetailByIdnum(String idnum) {
        String sql="SELECT\n" +
                "\tuserinfo1.name,\n" +
                "\t(SELECT COUNT(*) FROM warnrecord WHERE warnrecord.idnumber=?) as warnTimes,\n" +
                "\t'无' as 'illhis',\n" +
                "\tTIMESTAMPDIFF(YEAR,userinfo1.birthtime,CURDATE())AS 'age',\n" +
                "\tuserinfo1.nurselevel,\n" +
                "\twarnrecord.warnaddress\n" +
                "FROM warnrecord INNER JOIN userinfo1 ON warnrecord.idnumber=userinfo1.idnum\n" +
                "WHERE warnrecord.idnumber=? AND warnrecord.status=0\n" +
                "ORDER BY warntime DESC\n" +
                "LIMIT 1";
        return jdbcTemplate.queryForObject(sql, new Object[]{idnum, idnum}, new RowMapper<WarnDetail>() {
            @Nullable
            @Override
            public WarnDetail mapRow(ResultSet resultSet, int i) throws SQLException {
                return new WarnDetail(
                        resultSet.getString(1),
                        resultSet.getInt(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6)
                );
            }
        });
    }

    /**
     * 根据详细信息更新报警信息记录
     * @param admin 管理员用户名 'admin1'
     * @param idnum 身份证号码 '410823192104288891'
     * @param warntime 报警时间 '2021-05-22 18:20:56'
     * @param lon 经度 '113.253'
     * @param lat 纬度 '35.1871'
     * @return 影响行数
     */
    @Override
    public int updateWarnRecord(String admin, String idnum, String warntime, String lon, String lat) {
        String sql="UPDATE warnrecord SET status=1,adminaccount=?,endtime=NOW()\n" +
                "WHERE idnumber=? AND warntime=? AND TRIM(wlon)=? AND TRIM(wlat)=?";
        //执行更新语句
        return jdbcTemplate.update(sql, new Object[]{admin, idnum, warntime, lon, lat});
    }


    /**
     * @param sqlQuery SQL查询语句
     * @return List集合存储的WarnRecordEntity查询结果
     * */
    public List<WarnRecordEntity> solveResulotionBySql(String sqlQuery){
        //记录结果集
        List<WarnRecordEntity> list=new ArrayList<>();
        //声明中间变量
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try{
            connection = JDBCPGUtils.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sqlQuery);
            //解析结果集
            while (resultSet.next()){
                list.add(new WarnRecordEntity(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getFloat(4),
                        resultSet.getFloat(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getString(9)
                ));
            }
            //打印结果
//            System.out.println(JSON.toJSONString(list));
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return list;
        } finally {
            //释放资源
            JDBCPGUtils.close(resultSet,statement,connection);
        }
    }

    /**
     * @param rectangleVecs 矩形外边界
     * @return 拼接好的SQL语句
     * */
    public String getQuerySqlByRectangleVecs(String rectangleVecs){
//        String rectangleVecs="[{\"lon\":113.20975917546632,\"lat\":35.252547826257405},{\"lon\":113.27571380840526,\"lat\":35.23208473009601}]";
        JSONArray jsonArray = JSON.parseArray(rectangleVecs);
        Float[] lonArrs=new Float[jsonArray.size()];//经度数组
        Float[] latArrs=new Float[jsonArray.size()];//纬度数组
        //获取经/纬度数组元素
        for (int i = 0; i < jsonArray.size(); i++) {
            //将单个元素转换为JSONObject
            JSONObject jsonObject = JSON.parseObject(jsonArray.get(i).toString());
            lonArrs[i]=Float.parseFloat(jsonObject.get("lon").toString());
            latArrs[i]=Float.parseFloat(jsonObject.get("lat").toString());
        }
        //排序结果
        Float[] sortedLonArrs = ArrayUtils.insertSort(lonArrs);
        Float[] sortedLatArrs = ArrayUtils.insertSort(latArrs);
        //获取经度最大值、最小值
        float maxLon=sortedLonArrs[sortedLonArrs.length-1],
                minLon=sortedLonArrs[0];
        //获取纬度最大值、最小值
        float maxLat=sortedLatArrs[sortedLatArrs.length-1],
                minLat=sortedLatArrs[0];
        //拼接SQL字符串
        String sqlQuery="select \n" +
                "\tROW_NUMBER() over(ORDER bY warnrecord.idnumber DESC)  as id,\n" +
                "\twarnrecord.idnumber,\n" +
                "\tuserinfo1.name,\n" +
                "\twarnrecord.wlon,\n" +
                "\twarnrecord.wlat,\n" +
                "\twarnrecord.warntime,\n" +
                "\twarnrecord.status,\n" +
                "\twarnrecord.endtime,\n" +
                "\twarnrecord.warnaddress\n" +
                " from warnrecord,userinfo1,\n" +
                " \t\t(select ST_GeomFromText('POLYGON((";
        sqlQuery+=(minLon+" "+minLat+","+maxLon+" "+maxLat+","+maxLon+" "+maxLat+","+maxLon+" "+minLat+","+minLon+" "+minLat);
        sqlQuery+="))', 4326) as geom) A\n" +
                " where st_within(st_lonlatToGeomPoint(warnrecord.wlon,warnrecord.wlat),A.geom)='t' and userinfo1.idnum=warnrecord.idnumber";
//        System.out.println(sqlQuery);
        return sqlQuery;
    }

    /**
     * @param requestString 请求体解析
     * @return 返回拼接好的sql字符串
     * */
    public String getQuerySqlByRequestSrting(String requestString){
        //String 字符串转JSONArray实例
        /*
        "[\n" +
                "            {\"lon\":113.20531479663872,\"lat\":35.244323662970004},\n" +
                "            {\"lon\":113.21248425810958,\"lat\":35.26318146665559},\n" +
                "            {\"lon\":113.22948530864656,\"lat\":35.235440713031856},\n" +
                "            {\"lon\":113.22166377200492,\"lat\":35.22103232836536},\n" +
                "            {\"lon\":113.1977076831773,\"lat\":35.235988357217536}\n" +
                "        ]"
        * */
        JSONArray jsonArray = JSON.parseArray(requestString);
        //记录起点
        JSONObject firstPt=null;
        //构造SQL语句
        String sql="select \n" +
                "\tROW_NUMBER() over(ORDER bY warnrecord.idnumber DESC)  as id,\n" +
                "\twarnrecord.idnumber,\n" +
                "\tuserinfo1.name,\n" +
                "\twarnrecord.wlon,\n" +
                "\twarnrecord.wlat,\n" +
                "\twarnrecord.warntime,\n" +
                "\twarnrecord.status,\n" +
                "\twarnrecord.endtime,\n" +
                "\twarnrecord.warnaddress\n" +
                " from warnrecord,userinfo1,\n" +
                " \t\t(select ST_GeomFromText('POLYGON((";
        //遍历JSONArray实例，拼接SQL语句
        for (int i = 0; i < jsonArray.size(); i++) {
            //记录起点
            if (i == 0) {
                firstPt = JSON.parseObject(jsonArray.get(i).toString());
            }
            //单个JSONArray数组元素转换为JSONObject实例
            JSONObject jsonObject = JSON.parseObject(jsonArray.get(i).toString());
            sql+=(jsonObject.get("lon")+" "+jsonObject.get("lat")+",");
        }
        //连接起点，构成闭合环
        if (firstPt!=null){
            sql+=(firstPt.get("lon")+" "+firstPt.get("lat"));
        }
        //SQL语句拼接
        sql+="))', 4326) as geom) A\n" +
                " where st_within(st_lonlatToGeomPoint(warnrecord.wlon,warnrecord.wlat),A.geom)='t' and userinfo1.idnum=warnrecord.idnumber";
        return sql;//返回拼接好的sql语句
    }
}
